import sqlite3
import os
from flask import Flask, request, jsonify
from flask_restx import Api, Resource, fields, Namespace
from typing import List, Dict, Any, Optional
from src.infrastructure.sqlite_repository import SQLiteEmailQARepository
from src.application.service import EmailQAService, TaskIdParser, SOPService, SOPIdParser
from src.application.task1 import get_email_qa_data
from src.infrastructure.mysql_connection import MySQLConnection
from src.infrastructure.mysql_email_pending_task_repository import MySQLEmailPendingTaskRepository
from src.application.email_pending_task_service import EmailPendingTaskService
from src.infrastructure.sqlite_sop_repository import SQLiteSOPRepository
from src.application.sop_service import SOPService
from src.infrastructure.sqlite_emailqa_sop_repository import SQLiteEmailQA_SOPRepository
from src.application.emailqa_sop_service import EmailQA_SOPService
from pathlib import Path

app = Flask(__name__)

# Configuration
DB_PATH = "emailqa1.db"
ENABLE_API_DOCS = os.getenv('ENABLE_API_DOCS', 'true').lower() == 'true'

# Initialize Flask-RESTx API with documentation control
api = Api(app, version='1.0', title='EmailQA API', description='EmailQA Processing System API', 
         doc='/docs/' if ENABLE_API_DOCS else False)

# Define API models for documentation
emailqa_model = api.model('EmailQA', {
    'id': fields.Integer(description='EmailQA ID'),
    'system_conversation_id': fields.String(description='System conversation ID'),
    'q_body': fields.String(description='Question body'),
    'q_subject': fields.String(description='Question subject'),
    'q_real_question': fields.String(description='Real question'),
    'q_question_subject': fields.String(description='Question subject'),
    'q_attachment_content': fields.String(description='Attachment content'),
    'customer_service_answer': fields.String(description='Customer service answer'),
    'context_email_conversation_history_summary': fields.String(description='Conversation history summary'),
    'context_order_info': fields.String(description='Order info'),
    'email_type': fields.String(description='Email type (platform/customer)')
})

emailqa_request_model = api.model('EmailQARequest', {
    'content_list': fields.List(fields.String, required=True, description='List of content strings with task IDs')
})

emailqa_create_model = api.model('EmailQACreate', {
    'system_conversation_id': fields.String(required=True, description='System conversation ID'),
    'q_body': fields.String(required=True, description='Question body'),
    'q_subject': fields.String(required=True, description='Question subject'),
    'q_real_question': fields.String(required=True, description='Real question'),
    'q_question_subject': fields.String(required=True, description='Question subject'),
    'q_attachment_content': fields.String(description='Attachment content'),
    'customer_service_answer': fields.String(required=True, description='Customer service answer'),
    'context_email_conversation_history_summary': fields.String(description='Conversation history summary'),
    'context_order_info': fields.String(description='Order info')
})

email_pending_task_model = api.model('EmailPendingTask', {
    'id': fields.Integer(description='Task ID'),
    'email_id': fields.String(required=True, description='Email ID'),
    'task_id': fields.String(required=True, description='Task ID'),
    'email_info_json': fields.String(description='Email info JSON'),
    'sop_json': fields.String(description='SOP JSON'),
    'generated_reply': fields.String(description='Generated reply'),
    'action_type': fields.String(description='Action type'),
    'created_by': fields.String(description='Creator')
})

sop_model = api.model('SOP', {
    'id': fields.Integer(description='SOP ID'),
    'subject': fields.String(required=True, description='SOP subject'),
    'sop': fields.String(required=True, description='SOP content'),
    'created_at': fields.String(description='Creation timestamp'),
    'created_by': fields.String(required=True, description='Creator')
})

sop_request_model = api.model('SOPRequest', {
    'subject': fields.String(required=True, description='SOP subject'),
    'sop': fields.String(required=True, description='SOP content'),
    'created_by': fields.String(required=True, description='Creator')
})

sop_query_model = api.model('SOPQuery', {
    'content_list': fields.List(fields.String, required=True, description='List of content strings with SOP IDs')
})

emailqa_sop_model = api.model('EmailQA_SOP', {
    'id': fields.Integer(description='Association ID'),
    'emailqa_id': fields.Integer(required=True, description='EmailQA ID'),
    'sop_id': fields.Integer(required=True, description='SOP ID'),
    'created_at': fields.String(description='Creation timestamp'),
    'created_by': fields.String(required=True, description='Creator')
})

emailqa_sop_request_model = api.model('EmailQA_SOPRequest', {
    'emailqa_id': fields.Integer(required=True, description='EmailQA ID'),
    'sop_id': fields.Integer(required=True, description='SOP ID'),
    'created_by': fields.String(required=True, description='Creator')
})

# Create namespaces
emailqa_ns = Namespace('emailqa', description='EmailQA operations')
email_pending_task_ns = Namespace('email_pending_task', description='Email pending task operations')
sop_ns = Namespace('sop', description='SOP operations')
emailqa_sop_ns = Namespace('emailqa_sop', description='EmailQA-SOP association operations')
health_ns = Namespace('health', description='Health check operations')

# Register namespaces
api.add_namespace(emailqa_ns, path='/api/emailqa')
api.add_namespace(email_pending_task_ns, path='/api/email_pending_task')
api.add_namespace(sop_ns, path='/api/sop')
api.add_namespace(emailqa_sop_ns, path='/api/emailqa_sop')
api.add_namespace(health_ns, path='/api/health')

# MySQL connection setup
mysql_connection = MySQLConnection()

# Initialize database and create tables if they don't exist
def initialize_mysql_database():
    """Initialize MySQL database and create tables if they don't exist"""
    try:
        if mysql_connection.initialize_database():
            print("MySQL database initialized successfully")
        else:
            print("Warning: Failed to initialize MySQL database")
    except Exception as e:
        print(f"Warning: MySQL database initialization failed: {e}")

def initialize_sqlite_database():
    """Initialize SQLite database and create tables if they don't exist"""
    try:
        # Initialize EmailQA table
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute('''
                CREATE TABLE IF NOT EXISTS emailqa (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    system_conversation_id TEXT,
                    q_body TEXT,
                    q_subject TEXT,
                    q_real_question TEXT,
                    q_question_subject TEXT,
                    q_attachment_content TEXT,
                    customer_service_answer TEXT,
                    context_email_conversation_history TEXT,
                    context_email_conversation_history_symmary TEXT,
                    context_order_info TEXT
                )
            ''')
            print("EmailQA table initialized successfully")
        
        # Initialize SOP table
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute('''
                CREATE TABLE IF NOT EXISTS sop (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    subject TEXT NOT NULL,
                    sop TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    created_by TEXT NOT NULL
                )
            ''')
            # Create indexes
            conn.execute('CREATE INDEX IF NOT EXISTS idx_sop_subject ON sop (subject)')
            conn.execute('CREATE INDEX IF NOT EXISTS idx_sop_created_at ON sop (created_at)')
            conn.execute('CREATE INDEX IF NOT EXISTS idx_sop_created_by ON sop (created_by)')
            print("SOP table initialized successfully")
        
        # Initialize EmailQA_SOP table
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute('''
                CREATE TABLE IF NOT EXISTS emailqa_sop (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    emailqa_id INTEGER NOT NULL,
                    sop_id INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    created_by TEXT NOT NULL,
                    
                    -- Foreign key constraints
                    FOREIGN KEY (emailqa_id) REFERENCES emailqa(id),
                    FOREIGN KEY (sop_id) REFERENCES sop(id),
                    
                    -- Unique constraint to prevent duplicate associations
                    UNIQUE (emailqa_id, sop_id)
                )
            ''')
            print("EmailQA_SOP table initialized successfully")
        
        print("SQLite database initialized successfully")
        
    except Exception as e:
        print(f"Warning: SQLite database initialization failed: {e}")

# Initialize database on startup
initialize_mysql_database()
initialize_sqlite_database()

# Setup repository and service
mysql_repo = MySQLEmailPendingTaskRepository(mysql_connection)
email_pending_task_service = EmailPendingTaskService(mysql_repo)

# Setup SOP repository and service (using unified database)
sop_repository = SQLiteSOPRepository(DB_PATH)
sop_service = SOPService(sop_repository)

# Setup EmailQA_SOP repository and service (using unified database)
emailqa_sop_repository = SQLiteEmailQA_SOPRepository(DB_PATH)
emailqa_sop_service = EmailQA_SOPService(emailqa_sop_repository)

# Setup EmailQA repository and service
emailqa_repository = SQLiteEmailQARepository(DB_PATH)
emailqa_service = EmailQAService(emailqa_repository)


@emailqa_ns.route('/')
class EmailQAList(Resource):
    @emailqa_ns.expect(emailqa_request_model)
    @emailqa_ns.response(200, 'Success')
    @emailqa_ns.response(400, 'Bad Request')
    @emailqa_ns.response(500, 'Internal Server Error')
    def post(self):
        """Get EmailQA data by task IDs"""
        try:
            data = request.get_json()
            if not data or 'content_list' not in data:
                return {'error': 'content_list is required'}, 400
            
            content_list = data['content_list']
            if not isinstance(content_list, list):
                return {'error': 'content_list must be a list'}, 400
            
            task_ids = TaskIdParser.parse_task_ids(content_list)
            
            if not task_ids:
                return {'error': 'No valid task_ids found'}, 400
            
            result = get_email_qa_data(DB_PATH, task_ids)
            
            return {
                'success': True,
                'data': result,
                'task_ids': task_ids,
                'count': len(result)
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@emailqa_ns.route('/create')
class EmailQACreate(Resource):
    @emailqa_ns.expect(emailqa_create_model)
    @emailqa_ns.response(201, 'Created')
    @emailqa_ns.response(400, 'Bad Request')
    @emailqa_ns.response(500, 'Internal Server Error')
    def post(self):
        """Create a new EmailQA record"""
        try:
            data = request.get_json()
            if data is None:
                return {'error': 'Request body is required'}, 400
            
            # Validate required fields
            required_fields = ['system_conversation_id', 'q_body', 'q_subject', 'q_real_question', 
                             'q_question_subject', 'customer_service_answer']
            for field in required_fields:
                if field not in data or not data[field]:
                    return {'error': f'{field} is required'}, 400
            
            # Create EmailQA record
            result = emailqa_service.create_email_qa(data)
            
            return {
                'success': True,
                'message': 'EmailQA record created successfully',
                'data': result
            }, 201
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

# Keep the original route for backward compatibility
@app.route('/api/emailqa', methods=['POST'])
def get_emailqa_by_task_ids_legacy():
    """Legacy route for backward compatibility"""
    try:
        data = request.get_json()
        if not data or 'content_list' not in data:
            return jsonify({'error': 'content_list is required'}), 400
        
        content_list = data['content_list']
        if not isinstance(content_list, list):
            return jsonify({'error': 'content_list must be a list'}), 400
        
        task_ids = TaskIdParser.parse_task_ids(content_list)
        
        if not task_ids:
            return jsonify({'error': 'No valid task_ids found'}), 400
        
        result = get_email_qa_data(DB_PATH, task_ids)
        
        return jsonify({
            'success': True,
            'data': result,
            'task_ids': task_ids,
            'count': len(result)
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


# Keep the original route for backward compatibility
@app.route('/api/emailqa/create', methods=['POST'])
def create_emailqa_legacy():
    """Legacy route for creating EmailQA records"""
    try:
        data = request.get_json()
        if data is None:
            return jsonify({'error': 'Request body is required'}), 400
        
        # Validate required fields
        required_fields = ['system_conversation_id', 'q_body', 'q_subject', 'q_real_question', 
                         'q_question_subject', 'customer_service_answer']
        for field in required_fields:
            if field not in data or not data[field]:
                return jsonify({'error': f'{field} is required'}), 400
        
        # Create EmailQA record
        result = emailqa_service.create_email_qa(data)
        
        return jsonify({
            'success': True,
            'message': 'EmailQA record created successfully',
            'data': result
        }), 201
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@email_pending_task_ns.route('/')
class EmailPendingTaskList(Resource):
    @email_pending_task_ns.expect(email_pending_task_model)
    @email_pending_task_ns.response(201, 'Created')
    @email_pending_task_ns.response(400, 'Bad Request')
    @email_pending_task_ns.response(500, 'Internal Server Error')
    def post(self):
        """Create a new email pending task"""
        try:
            data = request.get_json()
            if data is None:
                return {'error': 'Request body is required'}, 400
            
            # Validate required fields
            required_fields = ['email_id', 'task_id']
            for field in required_fields:
                if field not in data:
                    return {'error': f'{field} is required'}, 400
            
            # Extract fields
            email_id = data['email_id']
            task_id = data['task_id']
            email_info_json = data.get('email_info_json')
            sop_json = data.get('sop_json')
            generated_reply = data.get('generated_reply')
            action_type = data.get('action_type')
            created_by = data.get('created_by')
            
            # Create the email pending task
            task_id_result = email_pending_task_service.create_email_pending_task(
                email_id=email_id,
                task_id=task_id,
                email_info_json=email_info_json,
                sop_json=sop_json,
                generated_reply=generated_reply,
                action_type=action_type,
                created_by=created_by
            )
            
            return {
                'success': True,
                'message': 'Email pending task created successfully',
                'task_id': task_id_result,
                'data': {
                    'email_id': email_id,
                    'task_id': task_id,
                    'email_info_json': email_info_json,
                    'sop_json': sop_json,
                    'generated_reply': generated_reply,
                    'action_type': action_type,
                    'created_by': created_by
                }
            }, 201
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

# Keep the original route for backward compatibility
@app.route('/api/email_pending_task', methods=['POST'])
def create_email_pending_task_legacy():
    """Legacy route for backward compatibility"""
    try:
        data = request.get_json()
        if data is None:
            return jsonify({'error': 'Request body is required'}), 400
        
        # Validate required fields
        required_fields = ['email_id', 'task_id']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'{field} is required'}), 400
        
        # Extract fields
        email_id = data['email_id']
        task_id = data['task_id']
        email_info_json = data.get('email_info_json')
        sop_json = data.get('sop_json')
        generated_reply = data.get('generated_reply')
        action_type = data.get('action_type')
        created_by = data.get('created_by')
        
        # Create the email pending task
        task_id_result = email_pending_task_service.create_email_pending_task(
            email_id=email_id,
            task_id=task_id,
            email_info_json=email_info_json,
            sop_json=sop_json,
            generated_reply=generated_reply,
            action_type=action_type,
            created_by=created_by
        )
        
        return jsonify({
            'success': True,
            'message': 'Email pending task created successfully',
            'task_id': task_id_result,
            'data': {
                'email_id': email_id,
                'task_id': task_id,
                'email_info_json': email_info_json,
                'sop_json': sop_json,
                'generated_reply': generated_reply,
                'action_type': action_type,
                'created_by': created_by
            }
        }), 201
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


# SOP API Endpoints
@sop_ns.route('/')
class SOPList(Resource):
    @sop_ns.response(200, 'Success')
    @sop_ns.response(500, 'Internal Server Error')
    def get(self):
        """Get all SOPs as a list of dictionaries"""
        try:
            sops = sop_service.get_sops_as_dict_list()
            return {
                'success': True,
                'data': sops,
                'count': len(sops)
            }
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

    @sop_ns.expect(sop_request_model)
    @sop_ns.response(201, 'Created')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(500, 'Internal Server Error')
    def post(self):
        """Create a new SOP"""
        try:
            data = request.get_json()
            if not data:
                return {'error': 'Missing JSON data'}, 400
            
            # Validate required fields
            required_fields = ['subject', 'sop', 'created_by']
            for field in required_fields:
                if field not in data or not data[field].strip():
                    return {'error': f'{field} is required and cannot be empty'}, 400
            
            # Create SOP
            sop = sop_service.create_sop(
                subject=data['subject'],
                sop_content=data['sop'],
                created_by=data['created_by']
            )
            
            return {
                'success': True,
                'message': 'SOP created successfully',
                'data': sop.to_dict()
            }, 201
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@sop_ns.route('/<int:sop_id>')
class SOPDetail(Resource):
    @sop_ns.response(200, 'Success')
    @sop_ns.response(404, 'Not Found')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(500, 'Internal Server Error')
    def get(self, sop_id):
        """Get a SOP by ID as a dictionary"""
        try:
            sop = sop_service.get_sop_by_id_as_dict(sop_id)
            if sop:
                return {
                    'success': True,
                    'data': sop
                }
            else:
                return {'error': f'SOP with ID {sop_id} not found'}, 404
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

    @sop_ns.expect(sop_request_model)
    @sop_ns.response(200, 'Success')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(404, 'Not Found')
    @sop_ns.response(500, 'Internal Server Error')
    def put(self, sop_id):
        """Update an existing SOP"""
        try:
            data = request.get_json()
            if not data:
                return {'error': 'Missing JSON data'}, 400
            
            # At least one field must be provided
            if not any(field in data for field in ['subject', 'sop', 'created_by']):
                return {'error': 'At least one field (subject, sop, created_by) must be provided'}, 400
            
            # Update SOP
            sop = sop_service.update_sop(
                sop_id=sop_id,
                subject=data.get('subject'),
                sop_content=data.get('sop'),
                created_by=data.get('created_by')
            )
            
            return {
                'success': True,
                'message': 'SOP updated successfully',
                'data': sop.to_dict()
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

    @sop_ns.response(200, 'Success')
    @sop_ns.response(404, 'Not Found')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(500, 'Internal Server Error')
    def delete(self, sop_id):
        """Delete a SOP"""
        try:
            deleted = sop_service.delete_sop(sop_id)
            if deleted:
                return {
                    'success': True,
                    'message': f'SOP with ID {sop_id} deleted successfully'
                }
            else:
                return {'error': f'SOP with ID {sop_id} not found'}, 404
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@sop_ns.route('/search')
class SOPSearch(Resource):
    @sop_ns.param('subject', 'Search term for SOP subject')
    @sop_ns.response(200, 'Success')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(500, 'Internal Server Error')
    def get(self):
        """Search SOPs by subject"""
        try:
            subject = request.args.get('subject')
            if not subject:
                return {'error': 'subject query parameter is required'}, 400
            
            sops = sop_service.search_sops_by_subject(subject)
            return {
                'success': True,
                'data': [sop.to_dict() for sop in sops],
                'count': len(sops)
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@sop_ns.route('/creator/<string:created_by>')
class SOPByCreator(Resource):
    @sop_ns.response(200, 'Success')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(500, 'Internal Server Error')
    def get(self, created_by):
        """Get SOPs by creator"""
        try:
            if not created_by or not created_by.strip():
                return {'error': 'created_by parameter is required'}, 400
            
            sops = sop_service.get_sops_by_creator(created_by)
            return {
                'success': True,
                'data': [sop.to_dict() for sop in sops],
                'count': len(sops)
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@sop_ns.route('/query')
class SOPQuery(Resource):
    @sop_ns.expect(sop_query_model)
    @sop_ns.response(200, 'Success')
    @sop_ns.response(400, 'Bad Request')
    @sop_ns.response(500, 'Internal Server Error')
    def post(self):
        """Get SOP data by parsing IDs from content list"""
        try:
            data = request.get_json()
            if not data or 'content_list' not in data:
                return {'error': 'content_list is required'}, 400
            
            content_list = data['content_list']
            if not isinstance(content_list, list):
                return {'error': 'content_list must be a list'}, 400
            
            # Parse SOP IDs from content list
            sop_ids = SOPIdParser.parse_sop_ids(content_list)
            
            if not sop_ids:
                return {'error': 'No valid SOP IDs found'}, 400
            
            # Get SOP data by IDs
            result = sop_service.get_sop_by_ids(sop_ids)
            
            return {
                'success': True,
                'data': result,
                'sop_ids': sop_ids,
                'count': len(result)
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


# Keep the original routes for backward compatibility
@app.route('/api/sop', methods=['GET'])
def get_all_sops_legacy():
    """Legacy route for backward compatibility"""
    try:
        sops = sop_service.get_sops_as_dict_list()
        return jsonify({
            'success': True,
            'data': sops,
            'count': len(sops)
        })
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop/<int:sop_id>', methods=['GET'])
def get_sop_by_id_legacy(sop_id):
    """Legacy route for backward compatibility"""
    try:
        sop = sop_service.get_sop_by_id_as_dict(sop_id)
        if sop:
            return jsonify({
                'success': True,
                'data': sop
            })
        else:
            return jsonify({'error': f'SOP with ID {sop_id} not found'}), 404
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop', methods=['POST'])
def create_sop_legacy():
    """Legacy route for backward compatibility"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'Missing JSON data'}), 400
        
        # Validate required fields
        required_fields = ['subject', 'sop', 'created_by']
        for field in required_fields:
            if field not in data or not data[field].strip():
                return jsonify({'error': f'{field} is required and cannot be empty'}), 400
        
        # Create SOP
        sop = sop_service.create_sop(
            subject=data['subject'],
            sop_content=data['sop'],
            created_by=data['created_by']
        )
        
        return jsonify({
            'success': True,
            'message': 'SOP created successfully',
            'data': sop.to_dict()
        }), 201
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop/<int:sop_id>', methods=['PUT'])
def update_sop_legacy(sop_id):
    """Legacy route for backward compatibility"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'Missing JSON data'}), 400
        
        # At least one field must be provided
        if not any(field in data for field in ['subject', 'sop', 'created_by']):
            return jsonify({'error': 'At least one field (subject, sop, created_by) must be provided'}), 400
        
        # Update SOP
        sop = sop_service.update_sop(
            sop_id=sop_id,
            subject=data.get('subject'),
            sop_content=data.get('sop'),
            created_by=data.get('created_by')
        )
        
        return jsonify({
            'success': True,
            'message': 'SOP updated successfully',
            'data': sop.to_dict()
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop/<int:sop_id>', methods=['DELETE'])
def delete_sop_legacy(sop_id):
    """Legacy route for backward compatibility"""
    try:
        deleted = sop_service.delete_sop(sop_id)
        if deleted:
            return jsonify({
                'success': True,
                'message': f'SOP with ID {sop_id} deleted successfully'
            })
        else:
            return jsonify({'error': f'SOP with ID {sop_id} not found'}), 404
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop/search', methods=['GET'])
def search_sops_legacy():
    """Legacy route for backward compatibility"""
    try:
        subject = request.args.get('subject')
        if not subject:
            return jsonify({'error': 'subject query parameter is required'}), 400
        
        sops = sop_service.search_sops_by_subject(subject)
        return jsonify({
            'success': True,
            'data': [sop.to_dict() for sop in sops],
            'count': len(sops)
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop/creator/<string:created_by>', methods=['GET'])
def get_sops_by_creator_legacy(created_by):
    """Legacy route for backward compatibility"""
    try:
        if not created_by or not created_by.strip():
            return jsonify({'error': 'created_by parameter is required'}), 400
        
        sops = sop_service.get_sops_by_creator(created_by)
        return jsonify({
            'success': True,
            'data': [sop.to_dict() for sop in sops],
            'count': len(sops)
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/sop/query', methods=['POST'])
def query_sop_by_ids_legacy():
    """Legacy route for SOP query by IDs"""
    try:
        data = request.get_json()
        if not data or 'content_list' not in data:
            return jsonify({'error': 'content_list is required'}), 400
        
        content_list = data['content_list']
        if not isinstance(content_list, list):
            return jsonify({'error': 'content_list must be a list'}), 400
        
        # Parse SOP IDs from content list
        sop_ids = SOPIdParser.parse_sop_ids(content_list)
        
        if not sop_ids:
            return jsonify({'error': 'No valid SOP IDs found'}), 400
        
        # Get SOP data by IDs
        result = sop_service.get_sop_by_ids(sop_ids)
        
        return jsonify({
            'success': True,
            'data': result,
            'sop_ids': sop_ids,
            'count': len(result)
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@health_ns.route('/')
class HealthCheck(Resource):
    @health_ns.response(200, 'Success')
    @health_ns.response(500, 'Internal Server Error')
    def get(self):
        """Health check endpoint with database status"""
        try:
            # Get MySQL database information
            mysql_info = mysql_connection.get_database_info()
            
            # Get SQLite database information
            sqlite_info = get_sqlite_database_info()
            
            # Determine overall status
            mysql_connected = mysql_info.get('connected', False)
            sqlite_connected = sqlite_info.get('connected', False)
            
            if mysql_connected and sqlite_connected:
                status = 'healthy'
            elif mysql_connected or sqlite_connected:
                status = 'degraded'
            else:
                status = 'unhealthy'
            
            return {
                'status': status,
                'message': 'EmailQA API is running',
                'database': {
                    'mysql': mysql_info,
                    'sqlite': sqlite_info
                }
            }
        except Exception as e:
            return {
                'status': 'degraded',
                'message': 'EmailQA API is running but database check failed',
                'error': str(e)
            }, 500

# Keep the original route for backward compatibility
@app.route('/api/health', methods=['GET'])
def health_check_legacy():
    """Health check endpoint with database status"""
    try:
        # Get MySQL database information
        mysql_info = mysql_connection.get_database_info()
        
        # Get SQLite database information
        sqlite_info = get_sqlite_database_info()
        
        # Determine overall status
        mysql_connected = mysql_info.get('connected', False)
        sqlite_connected = sqlite_info.get('connected', False)
        
        if mysql_connected and sqlite_connected:
            status = 'healthy'
        elif mysql_connected or sqlite_connected:
            status = 'degraded'
        else:
            status = 'unhealthy'
        
        return jsonify({
            'status': status,
            'message': 'EmailQA API is running',
            'database': {
                'mysql': mysql_info,
                'sqlite': sqlite_info
            }
        })
    except Exception as e:
        return jsonify({
            'status': 'degraded',
            'message': 'EmailQA API is running but database check failed',
            'error': str(e)
        }), 500

def get_sqlite_database_info():
    """Get SQLite database information"""
    try:
        import sqlite3
        
        # Check main EmailQA database
        emailqa_tables = []
        try:
            with sqlite3.connect(DB_PATH) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT name, sql 
                    FROM sqlite_master 
                    WHERE type='table' 
                    ORDER BY name
                """)
                tables = cursor.fetchall()
                emailqa_tables = [{'table_name': table[0], 'table_sql': table[1]} for table in tables]
        except Exception as e:
            print(f"Error checking EmailQA SQLite database: {e}")
        
        # Check SOP table in unified database
        sop_tables = []
        try:
            with sqlite3.connect(DB_PATH) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT name, sql 
                    FROM sqlite_master 
                    WHERE type='table' AND name='sop'
                    ORDER BY name
                """)
                tables = cursor.fetchall()
                sop_tables = [{'table_name': table[0], 'table_sql': table[1]} for table in tables]
        except Exception as e:
            print(f"Error checking SOP table: {e}")
        
        # Check EmailQA_SOP table in unified database
        emailqa_sop_tables = []
        try:
            with sqlite3.connect(DB_PATH) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT name, sql 
                    FROM sqlite_master 
                    WHERE type='table' AND name='emailqa_sop'
                    ORDER BY name
                """)
                tables = cursor.fetchall()
                emailqa_sop_tables = [{'table_name': table[0], 'table_sql': table[1]} for table in tables]
        except Exception as e:
            print(f"Error checking EmailQA_SOP table: {e}")
        
        return {
            'connected': True,
            'databases': {
                'emailqa': {
                    'path': DB_PATH,
                    'tables': emailqa_tables,
                    'table_count': len(emailqa_tables)
                },
                'sop': {
                    'path': DB_PATH,
                    'tables': sop_tables,
                    'table_count': len(sop_tables)
                },
                'emailqa_sop': {
                    'path': DB_PATH,
                    'tables': emailqa_sop_tables,
                    'table_count': len(emailqa_sop_tables)
                }
            }
        }
        
    except Exception as e:
        return {
            'connected': False,
            'error': str(e)
        }


# EmailQA_SOP API Endpoints
@emailqa_sop_ns.route('/search')
class EmailQA_SOPSearch(Resource):
    @emailqa_sop_ns.param('subject', 'Search term for SOP subject')
    @emailqa_sop_ns.response(200, 'Success')
    @emailqa_sop_ns.response(400, 'Bad Request')
    @emailqa_sop_ns.response(500, 'Internal Server Error')
    def get(self):
        """Get SOPs with associated EmailQA data by SOP subject"""
        try:
            subject = request.args.get('subject')
            if not subject:
                return {'error': 'subject query parameter is required'}, 400
            
            results = emailqa_sop_service.get_sops_with_emailqa_by_subject(subject)
            return {
                'success': True,
                'data': results,
                'count': len(results)
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@emailqa_sop_ns.route('/')
class EmailQA_SOPList(Resource):
    @emailqa_sop_ns.response(200, 'Success')
    @emailqa_sop_ns.response(500, 'Internal Server Error')
    def get(self):
        """Get all EmailQA_SOP associations"""
        try:
            associations = emailqa_sop_service.get_associations_as_dict_list()
            return {
                'success': True,
                'data': associations,
                'count': len(associations)
            }
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

    @emailqa_sop_ns.expect(emailqa_sop_request_model)
    @emailqa_sop_ns.response(201, 'Created')
    @emailqa_sop_ns.response(400, 'Bad Request')
    @emailqa_sop_ns.response(500, 'Internal Server Error')
    def post(self):
        """Create a new EmailQA_SOP association"""
        try:
            data = request.get_json()
            if not data:
                return {'error': 'Missing JSON data'}, 400
            
            # Validate required fields
            required_fields = ['emailqa_id', 'sop_id', 'created_by']
            for field in required_fields:
                if field not in data:
                    return {'error': f'{field} is required'}, 400
            
            # Create association
            association = emailqa_sop_service.create_association(
                emailqa_id=data['emailqa_id'],
                sop_id=data['sop_id'],
                created_by=data['created_by']
            )
            
            return {
                'success': True,
                'message': 'EmailQA_SOP association created successfully',
                'data': association.to_dict()
            }, 201
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


@emailqa_sop_ns.route('/<int:association_id>')
class EmailQA_SOPDetail(Resource):
    @emailqa_sop_ns.response(200, 'Success')
    @emailqa_sop_ns.response(404, 'Not Found')
    @emailqa_sop_ns.response(400, 'Bad Request')
    @emailqa_sop_ns.response(500, 'Internal Server Error')
    def get(self, association_id):
        """Get an EmailQA_SOP association by ID"""
        try:
            association = emailqa_sop_service.get_association_by_id_as_dict(association_id)
            if association:
                return {
                    'success': True,
                    'data': association
                }
            else:
                return {'error': f'EmailQA_SOP association with ID {association_id} not found'}, 404
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

    @emailqa_sop_ns.expect(emailqa_sop_request_model)
    @emailqa_sop_ns.response(200, 'Success')
    @emailqa_sop_ns.response(400, 'Bad Request')
    @emailqa_sop_ns.response(404, 'Not Found')
    @emailqa_sop_ns.response(500, 'Internal Server Error')
    def put(self, association_id):
        """Update an existing EmailQA_SOP association"""
        try:
            data = request.get_json()
            if not data:
                return {'error': 'Missing JSON data'}, 400
            
            # At least one field must be provided
            if not any(field in data for field in ['emailqa_id', 'sop_id', 'created_by']):
                return {'error': 'At least one field (emailqa_id, sop_id, created_by) must be provided'}, 400
            
            # Update association
            association = emailqa_sop_service.update_association(
                association_id=association_id,
                emailqa_id=data.get('emailqa_id'),
                sop_id=data.get('sop_id'),
                created_by=data.get('created_by')
            )
            
            return {
                'success': True,
                'message': 'EmailQA_SOP association updated successfully',
                'data': association.to_dict()
            }
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500

    @emailqa_sop_ns.response(200, 'Success')
    @emailqa_sop_ns.response(404, 'Not Found')
    @emailqa_sop_ns.response(400, 'Bad Request')
    @emailqa_sop_ns.response(500, 'Internal Server Error')
    def delete(self, association_id):
        """Delete an EmailQA_SOP association"""
        try:
            deleted = emailqa_sop_service.delete_association(association_id)
            if deleted:
                return {
                    'success': True,
                    'message': f'EmailQA_SOP association with ID {association_id} deleted successfully'
                }
            else:
                return {'error': f'EmailQA_SOP association with ID {association_id} not found'}, 404
        
        except ValueError as e:
            return {'error': str(e)}, 400
        except Exception as e:
            return {'error': f'Internal server error: {str(e)}'}, 500


# Keep the original routes for backward compatibility
@app.route('/api/emailqa_sop/search', methods=['GET'])
def search_sops_with_emailqa_legacy():
    """Legacy route for backward compatibility"""
    try:
        subject = request.args.get('subject')
        if not subject:
            return jsonify({'error': 'subject query parameter is required'}), 400
        
        results = emailqa_sop_service.get_sops_with_emailqa_by_subject(subject)
        return jsonify({
            'success': True,
            'data': results,
            'count': len(results)
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/emailqa_sop', methods=['POST'])
def create_emailqa_sop_association_legacy():
    """Legacy route for backward compatibility"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'Missing JSON data'}), 400
        
        # Validate required fields
        required_fields = ['emailqa_id', 'sop_id', 'created_by']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'{field} is required'}), 400
        
        # Create association
        association = emailqa_sop_service.create_association(
            emailqa_id=data['emailqa_id'],
            sop_id=data['sop_id'],
            created_by=data['created_by']
        )
        
        return jsonify({
            'success': True,
            'message': 'EmailQA_SOP association created successfully',
            'data': association.to_dict()
        }), 201
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/emailqa_sop/<int:association_id>', methods=['PUT'])
def update_emailqa_sop_association_legacy(association_id):
    """Legacy route for backward compatibility"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'Missing JSON data'}), 400
        
        # At least one field must be provided
        if not any(field in data for field in ['emailqa_id', 'sop_id', 'created_by']):
            return jsonify({'error': 'At least one field (emailqa_id, sop_id, created_by) must be provided'}), 400
        
        # Update association
        association = emailqa_sop_service.update_association(
            association_id=association_id,
            emailqa_id=data.get('emailqa_id'),
            sop_id=data.get('sop_id'),
            created_by=data.get('created_by')
        )
        
        return jsonify({
            'success': True,
            'message': 'EmailQA_SOP association updated successfully',
            'data': association.to_dict()
        })
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/emailqa_sop/<int:association_id>', methods=['DELETE'])
def delete_emailqa_sop_association_legacy(association_id):
    """Legacy route for backward compatibility"""
    try:
        deleted = emailqa_sop_service.delete_association(association_id)
        if deleted:
            return jsonify({
                'success': True,
                'message': f'EmailQA_SOP association with ID {association_id} deleted successfully'
            })
        else:
            return jsonify({'error': f'EmailQA_SOP association with ID {association_id} not found'}), 404
    
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/emailqa_sop/<int:association_id>', methods=['GET'])
def get_emailqa_sop_association_legacy(association_id):
    """Legacy route for backward compatibility"""
    try:
        association = emailqa_sop_service.get_association_by_id_as_dict(association_id)
        if association:
            return jsonify({
                'success': True,
                'data': association
            })
        else:
            return jsonify({'error': f'EmailQA_SOP association with ID {association_id} not found'}), 404
    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


@app.route('/api/emailqa_sop', methods=['GET'])
def get_all_emailqa_sop_associations_legacy():
    """Legacy route for backward compatibility"""
    try:
        associations = emailqa_sop_service.get_associations_as_dict_list()
        return jsonify({
            'success': True,
            'data': associations,
            'count': len(associations)
        })
    except Exception as e:
        return jsonify({'error': f'Internal server error: {str(e)}'}), 500


if __name__ == '__main__':
    print("=" * 60)
    print("EmailQA API Server Starting...")
    print("=" * 60)
    print(f"[OK] API Documentation: {'ENABLED' if ENABLE_API_DOCS else 'DISABLED'}")
    if ENABLE_API_DOCS:
        print(f"[INFO] API Documentation URL: http://localhost:5000/docs/")
        print(f"[INFO] Swagger UI: http://localhost:5000/docs/swagger")
        print(f"[INFO] OpenAPI JSON: http://localhost:5000/docs/swagger.json")
    else:
        print(f"[INFO] API Documentation: Disabled (set ENABLE_API_DOCS=true to enable)")
    print(f"[INFO] Database: {DB_PATH}")
    print(f"[INFO] Server: http://localhost:5000")
    print("=" * 60)
    
    app.run(debug=True, host='0.0.0.0', port=5000)